package com.me.study.javaCore.jdbc;

import java.sql.*;

/**
 * PostgreSQL 工具类
 *
 * @author ME
 * @date 2020/10/23
 */
public class PgsqlUtil {

    public static void main(String[] args) throws SQLException {
        // String url = "jdbc:postgresql://139.199.200.207:2323/eboos?currentSchema=crm_user";
        String url = "jdbc:postgresql://139.199.200.207:2323/eboos?currentSchema=crm_user";
        String user = "enterprisedb";
        String password = "edb@123456";
        Connection connection = getNewConnection(url, user, password);
        Statement stmt = connection.createStatement();

        String sql = "SELECT room_guid FROM syn_room  WHERE room_guid= 'd11b93ca-26ab-46e9-b795-17b39621905a'";
        ResultSet resultSet = stmt.executeQuery(sql);
        boolean next = resultSet.next();
        System.out.println(resultSet.getString(1));

        sql = "INSERT INTO syn_room as t (room_guid,proj_guid,bld_guid,bld_name,unit,floor,room,zx_bz,status,hu_xing,room_struct,bproduct_type_name,bld_area,tn_area,is_virtual_room,cst_name,cst_guid_list,bl_rh_date,west,sight,jf_date,receive_date,mailing_date,sale_rent_table,rhqk,saleserviceproc,saleserviceproctime,time,deleted) VALUES ('1','07d29fe9-5cb8-4ff8-823a-1b5706e2da70','329307df-a985-e811-ab10-00505693ef05','5','','3','302','毛坯','签约','5B','2房2厅1卫','高层','77.6600','55.6600','0','张欢','FEC39A46-7B22-4155-8141-7EC2FE311D12','','','','2021-09-30 00:00:00','','','出售','','未办理','2019-11-28 00:00:00','2019-11-28 22:07:06',0)";
        System.out.println(stmt.executeUpdate(sql));
    }

    private volatile static Connection conn;

    static {
        try {
            Class.forName("org.postgresql.Driver");
        } catch (ClassNotFoundException e) {
            System.err.println("Load PostgreSQL Driver failed");
            e.printStackTrace();
        }
    }

    public static Connection getNewConnection(String url, String user, String password) throws SQLException {
        return DriverManager.getConnection(url, user, password);
    }

    public static Connection getSingleConnection(String url, String user, String password) throws SQLException {
        if (conn == null) {
            synchronized (PgsqlUtil.class) {
                if (conn == null) {
                    conn = DriverManager.getConnection(url, user, password);
                }
            }
        }
        return conn;
    }

    public static ResultSet executeQueryByStmt(Connection conn, String sql) throws SQLException {
        if (conn == null) {
            throw new RuntimeException("Please initialize 'Connection' first");
        }
        Statement stmt = conn.createStatement();
        return stmt.executeQuery(sql);
    }

    public static int executeUpdateByStmt(Connection conn, String sql) throws SQLException {
        if (conn == null) {
            throw new RuntimeException("Please initialize 'Connection' first");
        }
        Statement stmt = conn.createStatement();
        return stmt.executeUpdate(sql);
    }

    public static PreparedStatement getPreparedStatement(Connection conn, String sql) throws SQLException {
        if (conn == null) {
            throw new RuntimeException("Please initialize 'Connection' first");
        }
        return conn.prepareStatement(sql);
    }
}
